package certificate.controller;

import certificate.database.ConnectionDB;
import certificate.entity.Course;
import certificate.entity.Student;
import certificate.entity.StudentSubjectStudy;
import certificate.entity.Subject;
import certificate.model.MarkModel;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Controller to retrieve data and update from database <br>
 * Create MarkModel to display in view <br>
 * MarkController retrieve from Students,StudentCourseRegitry, Course,CourseSubjectInclude,Subject <br>
 * and update to StudentSubjectStudy
 * @author Khanh
 */
public class MarkController {
    
    Connection conn = null;
    PreparedStatement pstm = null;
    
    
    /**
     * Get only Student Object by idStudent <br>
     * Select from Students table by idStudent
     * @param idStudent
     * @return 
     *      null if no student
     *      Student Object if only Student return
     * @throws SQLException more than two student return
     * @author Khanh
     */
    public Student getStudentById(String idStudent) throws SQLException{
        Student student;
        conn = ConnectionDB.getConnection();
        pstm = conn.prepareStatement("SELECT * FROM Students WHERE idStudent = ?");
        pstm.setString(1, idStudent);
        
        ResultSet rs = pstm.executeQuery();
        if(!rs.next()) return null;
        else{
            student= new Student();
            student.setIdStudent(idStudent);
            student.setName(rs.getString("name"));
            student.setDob(rs.getString("dob"));
            student.setPassword(rs.getString("password"));
            student.setAddress(rs.getString("address"));
            student.setGender(rs.getInt("gender"));
        }
        if(rs.next()) throw new SQLException("Many two student of only idStudent");
        
        if(conn != null) {
            conn.close();
        }
        return student;
    } 
    
    
    /**
     * Get All Course of student by  idStudent <br>
     * Join Course with StudentCourseRegitry on idCourse
     * @param idStudent
     * @return List<Course>
     * @throws SQLException 
     */
    public List<Course> getCourseOfStudent(String idStudent) throws SQLException{
        List<Course> list = new ArrayList<Course>();
        conn = ConnectionDB.getConnection();
        pstm = conn.prepareStatement("SELECT C.idCourse AS idCourse, name, price, timeAlong FROM Course C JOIN Student_Course_Regitry SC ON C.idCourse = SC.idCourse WHERE SC.idStudent = ?  ");
        
        pstm.setString(1, idStudent);
        ResultSet rs = pstm.executeQuery();
        while (rs.next()) {
            Course c = new Course();
            c.setIdCourse(rs.getString("idCourse"));
            c.setName(rs.getString("name"));
            c.setPrice(rs.getFloat("price"));
            c.setTimeAlong(rs.getString("timeAlong"));
            list.add(c);
        } 
        
        if(conn != null){
            conn.close();
        }
        return list;
    }
    
    /**
     * Know idStudent and idCourse , find all Subject and mark of Student <br>
     * Join Student_Subject_Study with Subject with Course_Subject_Include to get subject id, subject name, mark 
     * @param idCourse
     * @param idStudent
     * @return 
     * @exception SQLException connection and sql retrieve fail
     */
    public List<StudentSubjectStudy> getAllStudentSubjectStudy(String idStudent, String idCourse) throws SQLException{
        List<StudentSubjectStudy> list = new ArrayList<StudentSubjectStudy>();
        String sql = " SELECT SSS.id AS id, CSI.idSubject, S.name AS nameSubject, time, mark "
                   + " FROM Student_Subject_Study SSS JOIN Subject S ON SSS.idSubject = S.idSubject "
                   + "                                JOIN Course_Subject_Include  CSI ON SSS.idSubject = CSI.idSubject "
                   + " WHERE SSS.idStudent = ? AND CSI.idCourse = ? ";
        conn = ConnectionDB.getConnection();
        pstm = conn.prepareStatement(sql);
        
        pstm.setString(1, idStudent);
        pstm.setString(2, idCourse);
        ResultSet rs = pstm.executeQuery();
        while (rs.next()) {
            StudentSubjectStudy sss = new StudentSubjectStudy();
            sss.setId(rs.getInt("id"));
            sss.setIdSubject(rs.getString("idSubject"));
                Subject s = new Subject();
                s.setIdSubject(rs.getString("idSubject"));
                s.setName(rs.getString("nameSubject"));
            sss.setSubject(s);
            sss.setTime(rs.getString("time"));
            sss.setMark(rs.getInt("mark"));
            list.add(sss);
        }
        
        if(conn != null){
            conn.close();
        }
        return list;
    }
    
    
    /**
     * Update mark by id after admin input mark into MarkModel Table
     * @return 
     *  true if update success
     *  false if update fail
     */
    public boolean updateModel(MarkModel model){
        Set<Integer> sets = model.getTblMark().getIndexChanges();
        List<StudentSubjectStudy> list = model.getTblMark().getLstSubjectStudent();
        try{
            conn = ConnectionDB.getConnection();
            conn.setAutoCommit(false);
            pstm = conn.prepareStatement("UPDATE Student_Subject_Study SET mark = ? WHERE id = ? ");
            
            for(int i : sets){
                StudentSubjectStudy sss = list.get(i);
                pstm.setInt(1, sss.getMark());
                pstm.setInt(2, sss.getId());
                pstm.addBatch();
            }
            int[] updateCounts = pstm.executeBatch();
            conn.commit();
            
        }catch(SQLException e){
            try{
                conn.rollback();
            }catch(Exception ex){}
            return false;
        }finally{ 
            if(conn != null) try {
                conn.close();
            } catch (SQLException ex) {
                
            }
        }
        return true;
    }
}
